-- Filename: les05_attack_demo.sql
-- Created: October 2, 2007
-- Creator: Jenny Tsai-Smith
-- Description: Code sample with SQL injection vulnerability via dynamic SQL
--              with concatenated user name and password in ALTER USER statement.


CONN / as sysdba

--
-- SQL injectable code
--
CREATE OR REPLACE PROCEDURE change_password(p_username IN VARCHAR2, 
                                            p_password IN VARCHAR2)
AS
v_stmt VARCHAR2(4000);
BEGIN
 v_stmt := 'ALTER USER '|| p_username || ' IDENTIFIED BY ' || p_password;

 DBMS_Output.Put_Line('SQL stmt: '|| v_stmt);

 EXECUTE IMMEDIATE v_stmt;

EXCEPTION WHEN OTHERS THEN
   RAISE;
END;
/

GRANT EXECUTE on change_password TO PUBLIC
/

CONN hr

SELECT * from user_users
/

EXEC sys.change_password('HR','hr default tablespace system quota unlimited on system')


SELECT * from user_users
/


CONN / as sysdba

CREATE OR REPLACE FUNCTION
toInternal(Id varchar2) RETURN varchar2 IS

Temp varchar2(40);

begin
Temp := trim(Id); 

-- See comments in text re trimming

-- Remove quotes

IF substr(Temp,1,1) = '"' AND
substr(Temp,length(Temp),1) = '"' then

Temp := substr(Temp,2,length(Temp)-2);

else

-- Not quoted, so make sure is upper case

Temp := nls_upper(Temp);

end IF;

RETURN Temp;
end;
/ 

--
-- Use DBMS_ASSERT to eliminate SQL injection risk
--
CREATE OR REPLACE PROCEDURE change_password(p_username IN VARCHAR2, 
                                            p_password IN VARCHAR2)
AS
v_stmt VARCHAR2(4000);
BEGIN
 v_stmt := 'ALTER USER '
            || sys.dbms_assert.enquote_name(
                      sys.dbms_assert.schema_name(toInternal(p_username)),FALSE) || 
            ' IDENTIFIED BY ' 
            || sys.dbms_assert.simple_sql_name(p_password);

 DBMS_Output.Put_Line('SQL stmt: '|| v_stmt);

 EXECUTE IMMEDIATE v_stmt;

EXCEPTION WHEN OTHERS THEN
   RAISE;
END;
/

-- Set HR's default tablespace back to USERS
ALTER USER hr
DEFAULT TABLESPACE users
/

-- HR's password had been set to HR
CONN hr

SELECT * from user_users
/

EXEC sys.change_password('hr','hr default tablespace system quota unlimited on system')

SELECT * from user_users
/

CONN sh

CONN hr

EXEC sys.change_password('SH','new_password')

CONN sh

CONN / AS sysdba

--
-- Use DBMS_ASSERT to eliminate SQL injection risk and invoker's 
-- rights to safeguard privileges.
CREATE OR REPLACE PROCEDURE change_password(p_username IN VARCHAR2, 
                                            p_password IN VARCHAR2)
AUTHID CURRENT_USER
AS
v_stmt VARCHAR2(4000);
BEGIN
 v_stmt := 'ALTER USER '
            || sys.dbms_assert.enquote_name(
                      sys.dbms_assert.schema_name(toInternal(p_username)),FALSE) || 
            ' IDENTIFIED BY ' 
            || sys.dbms_assert.simple_sql_name(p_password);

 DBMS_Output.Put_Line('SQL stmt: '|| v_stmt);

 EXECUTE IMMEDIATE v_stmt;

EXCEPTION WHEN OTHERS THEN
   RAISE;
END;
/

CONN hr

EXEC sys.change_password('SH','another_password')

CONN / as sysdba

ALTER USER hr IDENTIFIED BY &original_hr_password
DEFAULT TABLESPACE users
/

ALTER USER sh IDENTIFIED BY &original_sh_password
/

